﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Public;

namespace DAL
{
    public class ManageProductDAL
    {
        DBConnection cn = new DBConnection();
        public DataTable LoadProductDAL(string strSearch)
        {
            string sql = "SELECT p.productId as ID, p.barCode as Barcode, pn.productName as Name, "
                                + "br.brandName AS Brand, u.unitName as Unit, p.unitPrice as Price, p.isShow as IsShow, "
                                + "p.expiredDate as 'Exprired date', p.quantity as Quantity"
                        + " FROM Product p"
                            + " JOIN ProductName pn ON p.proNameId = pn.proNameId"
                            + " JOIN Brand br ON p.brandId = br.brandId"
                            + "	JOIN Unit u ON p.unitId = u.unitId"
                        + " WHERE p.isShow = 1 and pn.productName like '%"+strSearch+"%';";
            return cn.loadData(sql);
        }


        public DataSet LoadProductNameDAL()
        {
            string sql = "SELECT * FROM ProductName;";
            return cn.LoadDBComBox(sql);
        }

        public DataSet LoadBrandDAL()
        {
            string sql = "SELECT * FROM Brand;";
            return cn.LoadDBComBox(sql);
        }

        public DataSet LoadUnitDAL()
        {
            string sql = "SELECT * FROM Unit;";
            return cn.LoadDBComBox(sql);
        }

        public DataTable ProductNameDAL()
        {
            string sql = "SELECT * FROM ProductName;";
            return cn.loadData(sql);
        }

        public void DeleteProduct(int id)
        {
            string sql = "UPDATE Product SET isShow = 0 where productId = " + id + ";";
            cn.InsertToDB(sql);
        }
        public int UpdateProductDAL(int productId, string barcode, int proNameId, 
            int brandId, int unitId, int price, string expriredDate, int quantity)
        {
            string sql = "UPDATE Product SET "
                             + " barCode = '" + barcode + "',"
                             + " proNameId = "+proNameId+","
                             + " brandId = "+brandId+","
                             + " unitId = "+unitId+","
                             + " unitPrice = "+price+","
                             + " expiredDate = '"+expriredDate+"',"
                             + " quantity = "+quantity+" "
                //+ " productImage = ''"
                             + " WHERE productId = " + productId + ";";
            return cn.InsertToDB(sql);
        }

        public int UpdateProductDAL2(Product product)
        {
            int Nparameter = 8;
            string[] name = new string[Nparameter];
            object[] value = new object[Nparameter];
            name[0] = "@proNameId"; value[0] = product.ProductId;
            name[1] = "@barCode"; value[1] = product.BarCode;
            name[2] = "@proNameId"; value[2] = product.ProductName.PNameId;
            name[3] = "@brandId"; value[3] = product.Brand.BrandId;
            name[4] = "@unitId"; value[4] = product.Unit.UnitId;
            name[5] = "@unitPrice"; value[5] = product.UnitPrice;
            name[6] = "@expiredDate"; value[6] = product.ExpriredDate;
            name[7] = "@quantity"; value[7] = product.Quantity;
                        
            return cn.Update("UPDATE Product SET "
                             + " barCode = @barCode,"
                             + " proNameId = @proNameId,"
                             + " brandId = @brandId,"
                             + " unitId = @unitId',"
                             + " unitPrice = @unitPrice,"
                             + " expiredDate = @expiredDate,"
                             + " quantity = @quantity,"
                             //+ " productImage = ''"
                             + " WHERE productId = @proNameId",
                                    name, value, Nparameter);
        }
    }
}
